package Util;

import java.awt.BorderLayout;

import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.LayoutManager;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


import test1.Export;

 

public class jFrame5 extends JFrame{
	public 
	JPanel panel11;
	JPanel panel12;
	JPanel panel13;
	JPanel mainpanel1;
	JLabel myLabel3;
	JTextField  myTextField3;
	JTextField myTextField4;
	JButton Button1;
	JButton Button2;
	JButton Button3;
	JButton myButtonD;
	public jFrame5(){
		LayoutManager layoutManager = new GridLayout(4,1);
        setLayout(layoutManager);
        setTitle("书店销售系统");
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(400, 400, 485, 343);
		setLocationRelativeTo(null);
		mainpanel1= new JPanel(new GridLayout(4,1));
		myLabel3=new JLabel("查询消费并导出报表");
		 
		JButton Button1=new JButton("按每日查询消费");
        JButton  Button2=new JButton("按每月查询消费");
        Button1.addActionListener(new ActionListener(){

			@Override
			public void actionPerformed(ActionEvent arg0) {
				// TODO Auto-generated method stub
				try (java.sql.Connection conn   = Myutil.getConnection()) {
                    PreparedStatement pr=conn.prepareStatement("select sum(sale.saleamount*price*discount),saledate from book,sale,customer where book.bnum=sale.bnum and customer.cnum=sale.cnum group by saledate");
                ResultSet t =pr.executeQuery();
                int flag=0;
                Export r=new Export();
                    JFrame myFrame = new JFrame("结果显示");
                    
                    LayoutManager l = new BorderLayout();
                    myFrame.setLayout(l);
                    myFrame.setBounds(400,400,600,400);
                    JPanel myPanel = new JPanel();
                    JTextArea myTextArea = new JTextArea(15,50);
                    while(t.next()){
                        Object [] d=new Object[2];
                        d[0]=t.getFloat(1);
                        d[1]=t.getString(2);
                        myTextArea.append("消费:"+d[0]+"    日期:"+d[1]);
                        myTextArea.append("\n");
                        r.addDO(d);
                        flag=1;
                    }

                    JScrollPane myPanels = new JScrollPane( myTextArea);
                    Dimension size= myTextArea.getPreferredSize();
                    myPanels.setBounds(110,90,size.width,size.height);
                    myPanel.add(myPanels);
                    myFrame.add(myPanel,BorderLayout.CENTER);
                    JPanel myPanel2= new JPanel();
                    Button3 =new JButton("导出报表");
                    ActionListener actiona =new ActionListener() {
                        @Override
                        public void actionPerformed(ActionEvent e) {
                            r.exportToCsv("每日金额表");
                        }
                    };
                    Button3.addActionListener(actiona);
                    myPanel2.add(Button3);
                    myFrame.add(myPanel2,BorderLayout.SOUTH);
                    if(flag==0){
                        JOptionPane.showMessageDialog(null,"没有数据","提示",
                                JOptionPane.INFORMATION_MESSAGE);
                    }else{
                        myFrame.setVisible(true);
                    }


                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
			
        	
        });
        Button2.addActionListener(new ActionListener(){

			@Override
			public void actionPerformed(ActionEvent e) {
				// TODO Auto-generated method stub
				try ( java.sql.Connection conn = Myutil.getConnection()) {if(myTextField3.getText().equals("")){
                    JOptionPane.showMessageDialog(null,"年份为空","提示",
                            JOptionPane.INFORMATION_MESSAGE);
                }else {
                    PreparedStatement pr = conn.prepareStatement("select sum(sale.saleamount*price*discount) from book,sale,customer where book.bnum=sale.bnum and customer.cnum=sale.cnum and  saledate like?  ");

                    Export r = new Export();
                    JFrame myFrame = new JFrame("结果显示");
                    LayoutManager l = new BorderLayout();
                    myFrame.setLayout(l);
                    myFrame.setBounds(400, 400, 600, 400);
                    JPanel myPanel = new JPanel();
                    JTextArea myTextArea = new JTextArea(15, 50);
                    for (int i = 1; i <= 12; i++) {
                        if (i < 10) {
                            pr.setString(1, myTextField3.getText() + "-0" + i + "%");
                            ResultSet t = pr.executeQuery();
                            Object[] d = new Object[2];
                            if (t.next()) {
                                d[0] = t.getFloat(1);
                            } else {
                                d[0] = 0;
                            }
                            d[1] = myTextField3.getText() + "-0" + i;
                            myTextArea.append("消费:" + d[0] + "    日期:" + myTextField3.getText() + "-0" + i);
                            myTextArea.append("\n");
                            r.addDO(d);
                        } else {
                            pr.setString(1, myTextField3.getText() + "-" + i + "%");
                            ResultSet t = pr.executeQuery();
                            Object[] d = new Object[2];
                            if (t.next()) {
                                d[0] = t.getFloat(1);
                            } else {
                                d[0] = 0;
                            }
                            d[1] = myTextField3.getText() + "-" + i;
                            myTextArea.append("消费:" + d[0] + "    日期:" + myTextField3.getText() + "-" + i);
                            myTextArea.append("\n");
                            r.addDO(d);
                        }

                    }
                    JScrollPane myPanels = new JScrollPane(myTextArea);
                    Dimension size = myTextArea.getPreferredSize();
                    myPanels.setBounds(110, 90, size.width, size.height);
                    myPanel.add(myPanels);
                    myFrame.add(myPanel, BorderLayout.CENTER);
                    JPanel myPanel2 = new JPanel();
                    myButtonD = new JButton("导出报表");
                    ActionListener actiona = new ActionListener() {
                        @Override
                        public void actionPerformed(ActionEvent e) {
                            r.exportToCsv("每月金额表");
                        }
                    };
                    myButtonD.addActionListener(actiona);
                    myPanel2.add(myButtonD);
                    myFrame.add(myPanel2, BorderLayout.SOUTH);
                    myFrame.setVisible(true);
                }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
			
        	
        });
         
       
        myTextField3=new JTextField(8);
         
		panel11=new JPanel();
		panel12=new JPanel();
		panel11.add(Button1);
		panel11.add(myLabel3);
		panel12.add(Button2);
		panel12.add(myLabel3);
		panel12.add(myTextField3);
		add(panel11);
		add(panel12);
		setVisible(true);
			
		
	}
}
                
        
            
